# 检测模块 - 包含所有数据检测功能
from config import load_env_structure_setl, load_env_structure_mdtrt, load_env_hsp_abbr, load_env_tbl_name
from sqlalchemy import text

# 加载环境变量
(
    SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME
) = load_env_tbl_name()
(
    SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, 
    SETL_SETL_TIME, SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG
) = load_env_structure_setl()
(
    MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME, MDTRT_HSP_LV, 
    MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE, MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, 
    MDTRT_AGE, MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME, MDTRT_IN_DEPT, MDTRT_OUT_DEPT, 
    MDTRT_VALIDFLAG, MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK, MDTRT_OUTP_DX_CHECK
) = load_env_structure_mdtrt()
hspListRaw = load_env_hsp_abbr()

def inspect_a01_duplicate_mdtrt_ids(engine, check_results, setl_count):
    """A01: 测试就诊表的MDTRT_ID是否有重复"""
    print('============ A01：测试 MDTRT_ID 是否有重复 ============')
    
    sql = f"""
    SELECT {MDTRT_MDTRT_ID}, COUNT(*) AS count
    FROM {MDTRT_TBL_NAME} m
    WHERE EXISTS(
        SELECT 1 FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    )
    AND m.{MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_MDTRT_ID}
    HAVING COUNT(*) > 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        duplicate_ids = result.fetchall()
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA 
    WITH T0 AS (
        SELECT {MDTRT_MDTRT_ID}, COUNT(*) AS count
        FROM {MDTRT_TBL_NAME} m
        WHERE EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            AND s.{SETL_VALIDFLAG} = '1'
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
        GROUP BY {MDTRT_MDTRT_ID}
        HAVING COUNT(*) > 1
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A01' AS INSPECT_ID,
        '就诊表的MDTRT_ID重复' AS INSPECT_NAME,
        1 AS LV,
        t0.{SETL_MDTRT_ID}||', 数量: '||t0.count AS INFO
    FROM {SETL_TBL_NAME} s, t0
    WHERE s.{SETL_MDTRT_ID} = t0.{MDTRT_MDTRT_ID}
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A01，就诊表的MDTRT_ID重复: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "A01",
        "inspect_name": "测试就诊表的MDTRT_ID是否有重复",
        "sql": sql,
        "msg": "重复的MDTRT_ID",
        "info": [{"MDTRT_ID": row[0], "出现次数": row[1]} for row in duplicate_ids[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A01：测试 MDTRT_ID 是否有重复 FINISHED ============')

def inspect_a02_invalid_hsp_ids(engine, check_results, setl_count):
    """A02: 测试HSP_ID是否有某个不存在"""
    print('============ A02：测试 HSP_ID 是否有不存在 ============')

    hspCodesSql = ", ".join([f"'{hsp[0]}'" for hsp in hspListRaw])
    
    sql = f"""
    SELECT DISTINCT {MDTRT_HSP_ID}
    FROM {MDTRT_TBL_NAME} m
    WHERE {MDTRT_HSP_ID} NOT IN ('[placeholder]', {hspCodesSql})
        AND EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            AND s.{SETL_VALIDFLAG} = '1'
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_hsp_ids = result.fetchall()
    
    if invalid_hsp_ids:
        print("不存在的HSP_ID:")
        for row in invalid_hsp_ids[:20]:
            print(row[0])
    
    check_results.append({
        "inspect_id": "A02",
        "inspect_name": "测试HSP_ID是否有某个不存在",
        "sql": sql,
        "msg": "不存在的HSP_ID",
        "info": [{"HSP_ID": row[0]} for row in invalid_hsp_ids],
        'count': None,
        'ocp': None,
    })
    
    print('============ A02：测试 HSP_ID 是否有不存在 FINISHED ============')

def inspect_a03_hsp_lv_consistency(engine, check_results, setl_count):
    """A03: 测试每一个HSP_ID，都有且只有一个HSP_LV"""
    print('============ A03：测试每一个HSP_ID，都有一个HSP_LV ============')
    
    sql = f"""
    SELECT {MDTRT_HSP_ID}, COUNT(DISTINCT {MDTRT_HSP_LV}) AS hsp_lv_count
    FROM {MDTRT_TBL_NAME} m
    WHERE EXISTS(
        SELECT 1 FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND s.{SETL_VALIDFLAG} = '1'
    )
    AND m.{MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_HSP_ID}
    HAVING COUNT(DISTINCT {MDTRT_HSP_LV}) = 0 OR COUNT(DISTINCT {MDTRT_HSP_LV}) > 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_hsp_lv = result.fetchall()
    
    if invalid_hsp_lv:
        print("无效的HSP_LV:")
        for row in invalid_hsp_lv[:20]:
            print(f"{row[0]} HSP_LV数量: {row[1]}")
    
    check_results.append({
        "inspect_id": "A03",
        "inspect_name": "测试每一个HSP_ID，都有一个HSP_LV",
        "sql": sql,
        "msg": "无效的HSP_LV",
        "info": [{"HSP_ID": row[0], "HSP_LV数量": row[1]} for row in invalid_hsp_lv[:20]],
        'count': None,
        'ocp': None,
    })
    
    print('============ A03：测试每一个HSP_ID，都有一个HSP_LV FINISHED ============')

def inspect_a04_certificate_consistency(engine, check_results, setl_count):
    """A04: 测试每一个人员都有一个人员编号"""
    print('============ A04：测试每一个人员都有一个人员编号 ============')
    
    # 如果MDTRT_CERTNO或MDTRT_CERT_TYPE不存在，则跳过检测
    if not MDTRT_CERTNO or not MDTRT_CERT_TYPE:
        print("MDTRT_CERTNO或MDTRT_CERT_TYPE未定义，跳过A04检测")
        return
    
    sql = f"""
    SELECT {MDTRT_CERTNO}, {MDTRT_CERT_TYPE}, COUNT(DISTINCT {MDTRT_PSN_NO}) AS psn_no_count
    FROM {MDTRT_TBL_NAME} m
    WHERE EXISTS(
        SELECT 1 FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND s.{SETL_VALIDFLAG} = '1'
    )
    AND m.{MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_CERTNO}, {MDTRT_CERT_TYPE}
    HAVING COUNT(DISTINCT {MDTRT_PSN_NO}) = 0 OR COUNT(DISTINCT {MDTRT_PSN_NO}) > 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_certificates = result.fetchall()
    
    # if invalid_certificates:
    #     print("无效的证件信息:")
    #     for row in invalid_certificates[:20]:
    #         print(f"证件号码: {row[0]}, 证件类型: {row[1]}, 人员编号数量: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO dirty_data 
    WITH T0a AS (
        SELECT {MDTRT_CERTNO}, {MDTRT_CERT_TYPE}, COUNT(DISTINCT {MDTRT_PSN_NO}) AS psn_no_count
        FROM {MDTRT_TBL_NAME} m
        WHERE EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            AND s.{SETL_VALIDFLAG} = '1'
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
        GROUP BY {MDTRT_CERTNO}, {MDTRT_CERT_TYPE}
        HAVING COUNT(DISTINCT {MDTRT_PSN_NO}) = 0 OR COUNT(DISTINCT {MDTRT_PSN_NO}) > 1
    )
    ,T0 AS (
        SELECT T0a.*, m.{MDTRT_MDTRT_ID}
        FROM T0a, {MDTRT_TBL_NAME} m
        WHERE T0a.{MDTRT_CERTNO} = m.{MDTRT_CERTNO} AND T0a.{MDTRT_CERT_TYPE} = m.{MDTRT_CERT_TYPE}
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A04' AS INSPECT_ID,
        '就诊表中同一个CENTNO有多个PSN_NO' AS INSPECT_NAME,
        3 AS LV,
        T0.{MDTRT_CERTNO} || ', ' || T0.{MDTRT_CERT_TYPE} || ', 人员编号数量: ' || T0.psn_no_count AS INFO
    FROM
        {SETL_TBL_NAME} s, T0
    WHERE
        s.{SETL_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A04，就诊表中同一个CENTNO有多个PSN_NO: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "A04",
        "inspect_name": "测试每一个人员都有一个人员编号",
        "sql": sql,
        "msg": "无效的证件信息",
        "info": [{"证件号码": row[0], "证件类型": row[1], "人员编号数量": row[2]} for row in invalid_certificates[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A04：测试每一个人员都有一个人员编号 FINISHED ============')

def inspect_a05_person_name_consistency(engine, check_results, setl_count):
    """A05: 测试每一个人员编号都有一个人员名称"""
    print('============ A05：测试每一个人员编号都有一个人员名称 ============')
    
    # 如果MDTRT_PSN_NAME不存在，则跳过检测
    if not MDTRT_PSN_NAME:
        print("MDTRT_PSN_NAME未定义，跳过A05检测")
        return
    
    sql = f"""
    SELECT {MDTRT_PSN_NO}, COUNT(DISTINCT {MDTRT_PSN_NAME}) AS psn_name_count
    FROM {MDTRT_TBL_NAME} m
    WHERE EXISTS(
        SELECT 1 FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND s.{SETL_VALIDFLAG} = '1'
    )
    AND m.{MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_PSN_NO}
    HAVING COUNT(DISTINCT {MDTRT_PSN_NAME}) = 0 OR COUNT(DISTINCT {MDTRT_PSN_NAME}) > 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_psn_ids = result.fetchall()
    
    # if invalid_psn_ids:
    #     print("无效的人员编号:")
    #     for row in invalid_psn_ids[:20]:
    #         print(f"人员编号: {row[0]}, 人员名称数量: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO dirty_data 
    WITH T0 AS (
        SELECT {MDTRT_PSN_NO}, COUNT(DISTINCT {MDTRT_PSN_NAME}) AS psn_name_count,
            max({MDTRT_MDTRT_ID}) AS {MDTRT_MDTRT_ID}
        FROM {MDTRT_TBL_NAME} m
        WHERE EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            AND s.{SETL_VALIDFLAG} = '1'
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
        GROUP BY {MDTRT_PSN_NO}
        HAVING COUNT(DISTINCT {MDTRT_PSN_NAME}) = 0 OR COUNT(DISTINCT {MDTRT_PSN_NAME}) > 1
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A05' AS INSPECT_ID,
        '同一人员编号有多个人员名称' AS INSPECT_NAME,
        3 AS LV,
        T0.{MDTRT_PSN_NO} || ', 人员名称数量: ' || T0.psn_name_count AS INFO
    FROM
        {SETL_TBL_NAME} s, T0
    WHERE
        s.{SETL_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A05，同一人员编号有多个人员名称: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "A05",
        "inspect_name": "测试每一个人员编号都有一个人员名称",
        "sql": sql,
        "msg": "无效的人员编号",
        "info": [{"人员编号": row[0], "人员名称数量": row[1]} for row in invalid_psn_ids[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A05：测试每一个人员编号都有一个人员名称 FINISHED ============')

def inspect_a06_gender_consistency(engine, check_results, setl_count):
    """A06: 测试每一个人员编号都有一个性别"""
    print('============ A06：测试每一个人员编号都有一个性别 ============')
    
    sql = f"""
    SELECT {MDTRT_PSN_NO}, COUNT(DISTINCT {MDTRT_GEND0}) AS psn_gender_count
    FROM {MDTRT_TBL_NAME} m
    WHERE EXISTS(
        SELECT 1 FROM {SETL_TBL_NAME} s
        WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND s.{SETL_VALIDFLAG} = '1'
    )
    AND m.{MDTRT_VALIDFLAG} = '1'
    GROUP BY {MDTRT_PSN_NO}
    HAVING COUNT(DISTINCT {MDTRT_GEND0}) = 0 OR COUNT(DISTINCT {MDTRT_GEND0}) > 1
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_psn_genders = result.fetchall()
    
    # if invalid_psn_genders:
    #     print("无效的人员性别:")
    #     for row in invalid_psn_genders[:20]:
    #         print(f"人员编号: {row[0]}, 性别数量: {row[1]}")

    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO dirty_data 
    WITH T0 AS (
        SELECT {MDTRT_PSN_NO}, COUNT(DISTINCT {MDTRT_GEND0}) AS psn_gender_count,
            max({MDTRT_MDTRT_ID}) AS {MDTRT_MDTRT_ID}
        FROM {MDTRT_TBL_NAME} m
        WHERE EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            AND s.{SETL_VALIDFLAG} = '1'
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
        GROUP BY {MDTRT_PSN_NO}
        HAVING COUNT(DISTINCT {MDTRT_GEND0}) = 0 OR COUNT(DISTINCT {MDTRT_GEND0}) > 1
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A06' AS INSPECT_ID,
        '同一人员编号有多个性别' AS INSPECT_NAME,
        2 AS LV,
        T0.{MDTRT_PSN_NO} || ', 性别数量: ' || T0.psn_gender_count AS INFO
    FROM
        {SETL_TBL_NAME} s, T0
    WHERE
        s.{SETL_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A06，同一人员编号有多个性别: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "A06",
        "inspect_name": "测试每一个人员编号都有一个性别",
        "sql": sql,
        "msg": "无效的人员性别",
        "info": [{"人员编号": row[0], "性别数量": row[1]} for row in invalid_psn_genders[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A06：测试每一个人员编号都有一个性别 FINISHED ============')

def inspect_a07_birth_date_consistency(engine, check_results, setl_count):
    """A07: 测试出生日期是否与证件中的出生日期一致"""
    print('============ A07：测试出生日期是否与证件中的出生日期一致 ============')
    # 如果MDTRT_BRDY或MDTRT_CERTNO不存在，则跳过检测
    if not MDTRT_CERTNO or not MDTRT_CERT_TYPE:
        print("MDTRT_CERT_TYPE未定义，跳过A07检测")
        return
    
    # 出生日期绝对值相差3天以上的
    sql = f"""
    SELECT {MDTRT_MDTRT_ID}, {MDTRT_CERTNO}, {MDTRT_BRDY}
    FROM {MDTRT_TBL_NAME} m
    WHERE {MDTRT_CERT_TYPE} IN ('01', '90')
        AND LENGTH({MDTRT_CERTNO}) > 15
        AND TO_NUMBER(SUBSTR({MDTRT_CERTNO}, 9, 2)) <= 12
        AND TO_DATE(SUBSTR({MDTRT_CERTNO}, 7, 8), 'YYYYMMDD') - {MDTRT_BRDY} > 3
        AND {MDTRT_BRDY} IS NOT NULL
        AND EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_birth_dates = result.fetchall()
    
    # if invalid_birth_dates:
    #     print("无效的出生日期:")
    #     for row in invalid_birth_dates[:20]:
    #         print(f"SETL_ID: {row[0]}, 人员编号: {row[1]}, 出生日期: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO dirty_data
    WITH T0 AS (
        SELECT {MDTRT_MDTRT_ID}, {MDTRT_CERTNO}, {MDTRT_BRDY}
        FROM {MDTRT_TBL_NAME} m
        WHERE {MDTRT_CERT_TYPE} IN ('01', '90')
            AND LENGTH({MDTRT_CERTNO}) > 15
            AND TO_NUMBER(SUBSTR({MDTRT_CERTNO}, 9, 2)) <= 12
            AND ABS(TO_DATE(SUBSTR({MDTRT_CERTNO}, 7, 8), 'YYYYMMDD') - {MDTRT_BRDY}) > 3
            AND {MDTRT_BRDY} IS NOT NULL
            AND EXISTS(
                SELECT 1 FROM {SETL_TBL_NAME} s
                WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
                AND s.{SETL_VALIDFLAG} = '1'
            )
            AND m.{MDTRT_VALIDFLAG} = '1'
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A07' AS INSPECT_ID,
        '出生日期与证件中的出生日期不一致' AS INSPECT_NAME,
        2 AS LV,
        T0.{MDTRT_MDTRT_ID} || ', ' || T0.{MDTRT_CERTNO} || ', 出生日期: ' || TO_CHAR(T0.{MDTRT_BRDY}, 'YYYY-MM-DD') AS INFO
    FROM T0
    JOIN {SETL_TBL_NAME} s ON s.{SETL_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A07，就诊表BRDY与CERTNO中的出生日期不一致: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()
    
    check_results.append({
        "inspect_id": "A07",
        "inspect_name": "测试出生日期是否与证件中的出生日期一致",
        "sql": sql,
        "msg": "无效的出生日期",
        "info": [{"SETL_ID": row[0], "人员编号": row[1], "出生日期": str(row[2])} for row in invalid_birth_dates[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A07：测试出生日期是否与证件中的出生日期一致 FINISHED ============')

# 测试性别是否与证件中的性别一致
def inspect_a08_gender_consistency(engine, check_results, setl_count):
    """A08: 测试性别是否与证件中的性别一致"""
    print('============ A08：测试性别是否与证件中的性别一致 ============')
    
    # 如果MDTRT_CERTNO或MDTRT_CERT_TYPE不存在，则跳过检测
    if not MDTRT_CERTNO or not MDTRT_CERT_TYPE:
        print("MDTRT_CERTNO或MDTRT_CERT_TYPE未定义，跳过A08检测")
        return
    
    sql = f"""
    SELECT {MDTRT_MDTRT_ID}, {MDTRT_CERTNO}, {MDTRT_GEND0}
    FROM {MDTRT_TBL_NAME} m
    WHERE {MDTRT_CERT_TYPE} IN ('01', '90')
        AND CASE
            WHEN {MDTRT_GEND0} in ('1','男','m') THEN SUBSTR({MDTRT_CERTNO}, 17, 1) IN ('1', '3', '5', '7', '9')
            WHEN {MDTRT_GEND0} in ('2','女','f') THEN SUBSTR({MDTRT_CERTNO}, 17, 1) IN ('0', '2', '4', '6', '8')
            ELSE FALSE
        END = FALSE
        AND {MDTRT_GEND0} IS NOT NULL
        AND EXISTS(
            SELECT 1 FROM {SETL_TBL_NAME} s
            WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        )
        AND m.{MDTRT_VALIDFLAG} = '1'
    """
    
    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_genders = result.fetchall()
        # if invalid_genders:
        #     print("无效的性别:")
        #     for row in invalid_genders[:20]:
        #         print(f"SETL_ID: {row[0]}, 人员编号: {row[1]}, 性别: {row[2]}")

    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO dirty_data
    WITH T0 AS (
        SELECT {MDTRT_MDTRT_ID}, {MDTRT_CERTNO}, {MDTRT_GEND0}
        FROM {MDTRT_TBL_NAME} m
        WHERE {MDTRT_CERT_TYPE} IN ('01', '90')
            AND CASE
                WHEN {MDTRT_GEND0} in ('1','男','m') THEN SUBSTR({MDTRT_CERTNO}, 17, 1) IN ('1', '3', '5', '7', '9')
                WHEN {MDTRT_GEND0} in ('2','女','f') THEN SUBSTR({MDTRT_CERTNO}, 17, 1) IN ('0', '2', '4', '6', '8')
                ELSE FALSE
            END = FALSE
            AND {MDTRT_GEND0} IS NOT NULL
            AND EXISTS(
                SELECT 1 FROM {SETL_TBL_NAME} s
                WHERE s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
            )
            AND m.{MDTRT_VALIDFLAG} = '1'
    )
    SELECT DISTINCT
        s.{SETL_SETL_ID} AS SETL_ID,
        'A08' AS INSPECT_ID,
        '测试性别是否与证件中的性别一致' AS INSPECT_NAME,
        2 AS LV,
        T0.{MDTRT_MDTRT_ID} || ', ' || T0.{MDTRT_CERTNO} || ', 性别: ' || T0.{MDTRT_GEND0} AS INFO
    FROM T0
    JOIN {SETL_TBL_NAME} s ON s.{SETL_MDTRT_ID} = T0.{MDTRT_MDTRT_ID}
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"A08，就诊表GEND与CERTNO中的性别不一致: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
    
    check_results.append({
        "inspect_id": "A08",
        "inspect_name": "测试性别是否与证件中的性别一致",
        "sql": sql,
        "msg": "无效的性别",
        "info": [{"SETL_ID": row[0], "人员编号": row[1], "性别": row[2]} for row in invalid_genders[:20]],
        'count': inserted_count if inserted_count is not None else 0,
        'ocp': inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ A08：测试性别是否与证件中的性别一致 FINISHED ============')
